package com.example.backend.mapper;

import com.example.backend.pojo.Data;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface DataMapper {
    @Select("SELECT \n" +
            "    dates.date,\n" +
            "    COALESCE(SUM(foodorder.price), 0) AS money,\n" +
            "    COALESCE(COUNT(foodorder.orderid), 0) AS number,\n" +
            "    COALESCE(SUM(foodorder.person), 0) AS person,\n" +
            "    COALESCE(AVG(foodorder.price), 0) AS avgprice\n" +
            "FROM \n" +
            "    (SELECT DATE('2024-04-01') + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS date\n" +
            "     FROM\n" +
            "        (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a\n" +
            "        CROSS JOIN\n" +
            "        (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b\n" +
            "        CROSS JOIN\n" +
            "        (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c\n" +
            "     WHERE DATE('2024-04-01') + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY <= CURRENT_DATE()) AS dates\n" +
            "LEFT JOIN \n" +
            "    foodorder ON DATE(foodorder.createtime) = dates.date AND foodorder.restaurantid = #{restaurantid}\n" +
            "GROUP BY \n" +
            "    dates.date\n" +
            "ORDER BY \n" +
            "    dates.date;")
    List<Data> daydata(Integer restaurantid);

    @Select("SELECT \n" +
            "    months.month,\n" +
            "    COALESCE(SUM(foodorder.price), 0) AS money,\n" +
            "    COALESCE(COUNT(foodorder.orderid), 0) AS number,\n" +
            "    COALESCE(SUM(foodorder.person), 0) AS person,\n" +
            "    COALESCE(AVG(foodorder.price), 0) AS avgprice\n" +
            "FROM \n" +
            "    (SELECT 4 AS month UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) AS months\n" +
            "LEFT JOIN \n" +
            "    foodorder ON MONTH(foodorder.createtime) = months.month AND foodorder.restaurantid = #{restaurantid}\n" +
            "WHERE \n" +
            "    YEAR(foodorder.createtime) = YEAR(CURRENT_DATE())  -- Filter by current year\n" +
            "    AND months.month <= MONTH(CURRENT_DATE())  -- Filter months until current month\n" +
            "GROUP BY \n" +
            "    months.month\n" +
            "ORDER BY \n" +
            "    months.month;\n")
    List<Data> monthdata(Integer restaurantid);


    @Select("SELECT \n" +
            "    dates.date,\n" +
            "    COALESCE(SUM(orderdetail.price), 0) AS money,\n" +
            "    COALESCE(SUM(orderdetail.number), 0) AS number\n" +
            "FROM \n" +
            "    (SELECT DISTINCT DATE(foodorder.createtime) AS date\n" +
            "     FROM foodorder) AS dates\n" +
            "LEFT JOIN \n" +
            "    foodorder ON DATE(foodorder.createtime) = dates.date\n" +
            "LEFT JOIN \n" +
            "    orderdetail ON foodorder.orderid = orderdetail.orderid AND orderdetail.foodid = #{foodid}\n" +
            "GROUP BY \n" +
            "    dates.date\n" +
            "ORDER BY \n" +
            "    dates.date;")
    List<Data> dayfood(Integer foodid);

    @Select("SELECT \n" +
            "    months.month,\n" +
            "    COALESCE(SUM(orderdetail.price), 0) AS money,\n" +
            "    COALESCE(SUM(orderdetail.number), 0) AS number\n" +
            "FROM \n" +
            "    (SELECT 4 AS month UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) AS months\n" +
            "LEFT JOIN \n" +
            "    foodorder ON MONTH(foodorder.createtime) = months.month\n" +
            "LEFT JOIN \n" +
            "    orderdetail ON foodorder.orderid = orderdetail.orderid AND orderdetail.foodid = #{foodid}\n" +
            "WHERE \n" +
            "    foodorder.createtime >= '2024-04-01' AND foodorder.createtime <= CURRENT_DATE()\n" +
            "GROUP BY \n" +
            "    months.month\n" +
            "ORDER BY \n" +
            "    months.month;\n")
    List<Data> monthfood(Integer foodid);

    @Select("SELECT  DATE(createtime) date,SUM(price) money ,Count(orderid) number,SUM(foodorder.person) person, AVG(foodorder.price) avgprice FROM foodorder GROUP BY DATE(createtime)")
    List<Data> allday();
    @Select("SELECT  month(createtime) month,SUM(price) money ,Count(orderid) number,SUM(foodorder.person) person, AVG(foodorder.price) avgprice FROM foodorder  GROUP BY month(createtime)")
    List<Data> allmonth();
    @Select("SELECT  SUM(price) money ,Count(orderid) number,SUM(foodorder.person) person, AVG(foodorder.price) avgprice FROM foodorder")
    Data allall();

    @Select("SELECT \n" +
            "    r.restaurantname name,\n" +
            "    COALESCE(SUM(fo.price), 0) AS money,\n" +
            "    COALESCE(COUNT(fo.orderid), 0) AS number,\n" +
            "    COALESCE(SUM(fo.person), 0) AS person,\n" +
            "    COALESCE(AVG(fo.price), 0) AS avgprice\n" +
            "FROM \n" +
            "    restaurant r\n" +
            "LEFT JOIN \n" +
            "    foodorder fo ON r.restaurantid = fo.restaurantid\n" +
            "GROUP BY \n" +
            "    r.restaurantid, r.restaurantname;\n")
    List<Data> allres();


    @Select("SELECT \n" +
            "    DATE_FORMAT(d.date, '%Y-%m') AS month,\n" +
            "    IFNULL(SUM(f.price), 0) AS money,\n" +
            "    IFNULL(COUNT(f.complainid), 0) AS number,\n" +
            "    IFNULL(COUNT(f.reviewid), 0) AS person\n" +
            "FROM (\n" +
            "    SELECT \n" +
            "        DATE_ADD('2024-04-01', INTERVAL (@i := @i + 1) DAY) AS date\n" +
            "    FROM \n" +
            "        (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a,\n" +
            "        (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b,\n" +
            "        (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c,\n" +
            "        (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS d,\n" +
            "        (SELECT @i := -1) vars\n" +
            "    WHERE \n" +
            "        DATE_ADD('2024-04-01', INTERVAL @i + 1 DAY) <= NOW()\n" +
            ") d\n" +
            "LEFT JOIN foodorder f \n" +
            "ON DATE(d.date) = DATE(f.createtime) AND f.userid = #{userid}\n" +
            "GROUP BY \n" +
            "    DATE_FORMAT(d.date, '%Y-%m')\n" +
            "ORDER BY \n" +
            "    DATE_FORMAT(d.date, '%Y-%m');\n")
    List<Data> usermonthdate(Integer userid);


    @Select("SELECT SUM(price) money ,COUNT(complainid) number,COUNT(reviewid) person  FROM `foodorder` where userid=#{userid} ")
    Data useralldate(Integer userid);

}
